﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MimosaProductsModel;
using MimosaProductsEF.Model;
using System.Data.OleDb;
using System.Data;
using Ionic.Zip;
using System.IO;

namespace MimosaProducts.Client
{
    class Program
    {
        static void Main(string[] args)
        {
            //TransferFromMysqlToSqlServer();
            MyExtract("..\\..\\Sales-Report.zip");
        }

        private static void TransferFromMysqlToSqlServer()
        {
            using (MimosaProductsEntities mysqlDb = new MimosaProductsEntities())
            {
                using (MimosaProductsEntitiesEF mssqlDb = new MimosaProductsEntitiesEF())
                {
                    foreach (var measure in mysqlDb.Measures)
                    {
                        var measureEF = new MimosaProductsEF.Model.Measure();
                        measureEF.MeasureId = measure.MeasureId;
                        measureEF.MeasureName = measure.MeasureName;

                        mssqlDb.Measures.Add(measureEF);
                    }

                    foreach (var vendor in mysqlDb.Vendors)
                    {
                        var vendorEF = new MimosaProductsEF.Model.Vendor();
                        vendorEF.VendorId = vendor.VendorId;
                        vendorEF.VendorName = vendor.VendorName;

                        mssqlDb.Vendors.Add(vendorEF);
                    }

                    foreach (var product in mysqlDb.Products)
                    {
                        var productEF = new MimosaProductsEF.Model.Product();
                        productEF.ProductId = product.ProductId;
                        productEF.VendorId = product.VendorId;
                        productEF.ProductName = product.ProductName;
                        productEF.MeasureId = product.MeasureId;
                        productEF.BasePrice = (float)product.BasePrice;

                        mssqlDb.Products.Add(productEF);
                    }

                    mssqlDb.SaveChanges();
                }
            }
        }

        private static void MyExtract(string zipToUnpack)
        {
            string unpackDirectory = GetTemporaryDirectory();
            using (ZipFile zip = ZipFile.Read(zipToUnpack))
            {
                foreach (ZipEntry e in zip)
                {
                    e.Extract(unpackDirectory, ExtractExistingFileAction.OverwriteSilently);
                }
            }

            RecursiveFileProcessor(unpackDirectory);
        }

        private static string GetTemporaryDirectory()
        {
            string tempDir = Path.Combine(
                Path.GetTempPath(),
                Path.GetRandomFileName());

            Directory.CreateDirectory(tempDir);

            return tempDir;
        }

        public static void RecursiveFileProcessor(string path)
        {
            if (File.Exists(path))
            {
                ProcessFile(path);
            }
            else if (Directory.Exists(path))
            {
                ProcessDirectory(path);
            }
            else
            {
                Console.WriteLine("{0} is not a valid file or directory.", path);
            }
        }

        public static void ProcessDirectory(string dir)
        {
            string[] fileEntries = Directory.GetFiles(dir);
            foreach (string fileName in fileEntries)
            {
                ProcessFile(fileName);
            }

            // Recurse into subdirectories
            string[] subdirectoryEntries = Directory.GetDirectories(dir);
            foreach (string subdirectory in subdirectoryEntries)
            {
                ProcessDirectory(subdirectory);
            }
        }

        public static void ProcessFile(string path)
        {
            DataTable sheet = new DataTable("Sales");

            var dbConnString = GetConnectionString(path);
            var dbConnection = new OleDbConnection(dbConnString);
            dbConnection.Open();
            using (dbConnection)
            {
                var command = new OleDbCommand("SELECT * FROM [Sales$];", dbConnection);

                using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                {
                    adapter.FillSchema(sheet, SchemaType.Source);
                    adapter.Fill(sheet);
                }

                int rowsCount = sheet.Rows.Count - 1;
                string locationStr = string.Empty;
                string date = path.Substring(path.LastIndexOf('\\', path.LastIndexOf('\\') - 1) + 1, 11);

                using (MimosaProductsEntitiesEF db = new MimosaProductsEntitiesEF())
                {
                    for (int i = 0; i < rowsCount; i++)
                    {
                        if (i == 1)
                        {
                            continue;
                        }
                        if (i == 0)
                        {
                            locationStr = sheet.Rows[i].ItemArray[0].ToString();
                        }
                        else
                        {
                            var productId = int.Parse(sheet.Rows[i].ItemArray[0].ToString());
                            var quantity = int.Parse(sheet.Rows[i].ItemArray[1].ToString());
                            var unitPrice = float.Parse(sheet.Rows[i].ItemArray[2].ToString());
                            var sum = float.Parse(sheet.Rows[i].ItemArray[3].ToString());

                            Sale sale = new Sale
                            {
                                ProductId = productId,
                                Quantity = quantity,
                                UnitPrice = unitPrice,
                                Sum = sum,
                                Date = date
                            };

                            var location = db.Locations.Where(x => x.LocationName == locationStr).ToList();
                            if (location.Count > 0)
                            {
                                sale.Location = location[0];
                            }
                            else
                            {
                                Location newLocation = new Location { LocationName = locationStr };
                                db.Locations.Add(newLocation);
                                sale.Location = newLocation;

                            }

                            db.Sales.Add(sale);
                            db.SaveChanges();
                        }
                    }
                }
            }
        }

        private static string GetConnectionString(string filePath)
        {
            try
            {
                var sbConnection = new OleDbConnectionStringBuilder();
                var strExtendedProperties = string.Empty;
                sbConnection.DataSource = filePath;

                if (Path.GetExtension(filePath).Equals(".xls")) // for 97-03 Excel file
                {
                    sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                    strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1"; // HDR = ColumnHeader, IMEX = InterMixed
                }
                else if (Path.GetExtension(filePath).Equals(".xlsx")) // for 2007 Excel file
                {
                    sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                    strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
                }

                sbConnection.Add("Extended Properties", strExtendedProperties);

                return sbConnection.ToString();
            }
            catch (Exception)
            {
                throw new ArgumentException("Such path does not exist!");
            }
        }
    }
}
